﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace SoundMatcher
{
    public static class DB
    {
        private static readonly string dataProvider = "System.Data.SqlClient";
        private static readonly DbProviderFactory factory = DbProviderFactories.GetFactory(dataProvider);
        private static readonly string connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=I:\\Final year project\\New code 1\\SoundMatcher\\SoundMatcher\\MSE_DB.mdf;Integrated Security=True;User Instance=True";
        public static readonly string databaseName = "MSE_DB";

        #region Data Update handlers

        public static int Update(string sql)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandText = sql;

                    connection.Open();
                    return command.ExecuteNonQuery();
                }
            }
        }

        public static int Update(System.Data.SqlClient.SqlCommand cmd)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                int result = -1;

                connection.ConnectionString = connectionString;

                if (connection is SqlConnection)
                {
                    cmd.Connection = (SqlConnection)connection;


                    connection.Open();
                    result = cmd.ExecuteNonQuery();


                }
                return result;
            }
        }

        public static int Insert(System.Data.SqlClient.SqlCommand cmd)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                int result = -1;

                connection.ConnectionString = connectionString;

                if (connection is SqlConnection)
                {
                    cmd.Connection = (SqlConnection)connection;


                    connection.Open();
                    result = cmd.ExecuteNonQuery();


                }
                return result;
            }

        }


        public static int Insert(string sql, bool getId)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandText = sql;

                    connection.Open();
                    command.ExecuteNonQuery();

                    int id = -1;

                    if (getId)
                    {
                        string identitySelect;
                        switch (dataProvider)
                        {
                            case "System.Data.OleDb":
                                identitySelect = "SELECT @@IDENTITY";
                                break;
                            case "System.Data.SqlClient":
                                identitySelect = "SELECT SCOPE_IDENTITY()";
                                break;
                            case "System.Data.OracleClient":
                                identitySelect = "SELECT MySequence.NEXTVAL FROM DUAL";
                                break;
                            default:
                                identitySelect = "SELECT @@IDENTITY";
                                break;
                        }
                        command.CommandText = identitySelect;
                        id = int.Parse(command.ExecuteScalar().ToString());
                    }
                    return id;
                }
            }
        }

        public static void Insert(string sql)
        {
            Insert(sql, false);
        }

        #endregion

        #region Data Retrieve Handlers

        public static DataSet GetDataSet(string sql)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.Text;
                    command.CommandText = sql;

                    using (DbDataAdapter adapter = factory.CreateDataAdapter())
                    {
                        adapter.SelectCommand = command;

                        DataSet ds = new DataSet();
                        adapter.Fill(ds);

                        return ds;
                    }
                }
            }
        }

        public static DataTable GetDataTable(string sql)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.Text;
                    command.CommandText = sql;

                    using (DbDataAdapter adapter = factory.CreateDataAdapter())
                    {
                        adapter.SelectCommand = command;

                        DataTable dt = new DataTable();
                        adapter.Fill(dt);

                        return dt;
                    }
                }
            }
            //using (SqlConnection connection = new SqlConnection())
            //{
            //    //return GetDataSet(sql).Tables[0];
            //    connection.ConnectionString = connectionString;

            //    using (SqlCommand command = new SqlCommand())
            //    {
            //        command.Connection = connection;
            //        command.CommandType = CommandType.Text;
            //        command.CommandText = sql;

            //        using (DbDataAdapter adapter = new SqlDataAdapter())
            //        {
            //            adapter.SelectCommand = command;

            //            DataTable dt = new DataTable();
            //            adapter.Fill(dt);

            //            return dt;
            //        }
            //    }
            //}
        }

        public static DataRow GetDataRow(string sql)
        {
            DataRow row = null;

            DataTable dt = GetDataTable(sql);
            if (dt.Rows.Count > 0)
            {
                row = dt.Rows[0];
            }

            return row;
        }

        public static object GetScalar(string sql)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandText = sql;

                    connection.Open();
                    return command.ExecuteScalar();
                }
            }
        }

        #endregion

        #region Utility methods

        public static string Escape(string s)
        {
            if (String.IsNullOrEmpty(s))
                return "NULL";
            else
                return "'" + s.Trim().Replace("'", "''") + "'";
        }

        public static string Escape(string s, int maxLength)
        {
            if (String.IsNullOrEmpty(s))
                return "NULL";
            else
            {
                s = s.Trim();
                if (s.Length > maxLength) s = s.Substring(0, maxLength - 1);
                return "'" + s.Trim().Replace("'", "''") + "'";
            }
        }

        #endregion
    }
}
